public class Category
    {
        private int categoryId = -1;
        private string categoryName;
        private string descripion;
        
        public int CategoryId
        {
            get { return categoryId; }
            set { categoryId = value; }
        }

        public string CategoryName
        {
            get { return categoryName; }
            set { categoryName = value; }
        }

        public string Description
        {
            get { return descripion; }
            set { descripion = value; }
        }

    }


 public class CategoryList : List<Category>
    {
        public CategoryList()
        {
        }
    }



    public class CategoryDB
    {
        public static CategoryList GetList()
        {
            CategoryList tempList = null;

            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

            string mySql = "select * from Categories  ";
            SqlCommand myCommand = new SqlCommand(mySql, myConnection);

            myConnection.Open();
            SqlDataReader myReader = null;

            myReader = myCommand.ExecuteReader();

            if (myReader.HasRows)
            {
                tempList = new CategoryList();

                while (myReader.Read())
                    tempList.Add(FillDataRecord(myReader));
            }

            myReader.Close();
            myConnection.Close();

            return tempList;

        }

        public static Category GetItem(int categoryId)
        {
            Category myCategory = new Category();

            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

            SqlCommand myCommand = new SqlCommand("sprocCategorySelectSingleItem", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.AddWithValue("@categoryId", categoryId);

            myConnection.Open();

            SqlDataReader myReader = myCommand.ExecuteReader();

            if (myReader.Read())
            {
                myCategory = FillDataRecord(myReader);
            }

            myReader.Close();
            myConnection.Close();

            return myCategory;
        }

        public static int Save(Category myCategory)
        {
            int result = 0;

            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

            SqlCommand myCommand = new SqlCommand("sprocCategoryUpdateSingleItem", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            if (myCategory.CategoryId == -1)
                myCommand.Parameters.AddWithValue("@categoryId", DBNull.Value);
            else
                myCommand.Parameters.AddWithValue("@categoryId", myCategory.CategoryId);

            myCommand.Parameters.AddWithValue("@categoryName", myCategory.CategoryName);
            myCommand.Parameters.AddWithValue("@description", myCategory.Description);

            DbParameter returnValue = myCommand.CreateParameter();
            returnValue.Direction = ParameterDirection.ReturnValue;
            myCommand.Parameters.Add(returnValue);

            myConnection.Open();

            myCommand.ExecuteNonQuery();
            result = Convert.ToInt32(returnValue.Value);

            myConnection.Close();

            return result;

        }

        public static bool Delete(Category myCategory)
        {
            int categoryId = myCategory.CategoryId;
            int result = 0;

            SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString);

            SqlCommand myCommand = new SqlCommand("sprocCategoryDeleteItem", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.AddWithValue("@categoryId", categoryId);

            myConnection.Open();

            result = myCommand.ExecuteNonQuery();

            myConnection.Close();

            return result > 0;
        }

        private static Category FillDataRecord(IDataRecord myDataRecord)
        {
            Category myCategory = new Category();

            myCategory.CategoryId = myDataRecord.GetInt32(myDataRecord.GetOrdinal("CategoryId"));
            myCategory.CategoryName = myDataRecord.GetString(myDataRecord.GetOrdinal("CategoryName"));
            myCategory.Description = myDataRecord.GetString(myDataRecord.GetOrdinal("Description"));

            return myCategory;
        }
    }




    public class CategoryManager
    {
        public static CategoryList GetList()
        {
            return CategoryDB.GetList();
        }

        public static Category GetItem(int categoryId)
        {
            return CategoryDB.GetItem(categoryId);
        }

        public static int Save(Category myCategory)
        {
            myCategory.CategoryId = CategoryDB.Save(myCategory);

            return myCategory.CategoryId;
        }

        public static bool Delete(Category myCategory)
        {
            return CategoryDB.Delete(myCategory);
        }


    }
